<?php

class ProjectTable extends Doctrine_Table
{
  public function getBudgetCostComparison(){
    $resourceCostPerMilestone = Doctrine_Query::create()
	  ->select('p.projectname, p.projectid, count(Distinct m.milestoneid) AS milestones, count(Distinct t.taskid) AS tasks, count(Distinct r.resourceid) AS resources, sum(IFNULL(r.rate,'.sfConfig::get('app_min_cost_per_resource').')*(IFNULL(hour(timediff(t.estimatedenddate,t.startdate)),0)+(IFNULL(minute(timediff(t.estimatedenddate,t.startdate))/60,0)))*'.sfConfig::get('app_working_hours_per_day').') AS cost, sum(m.estimatedcost) AS budget')
      ->from('Project p')
	  ->leftJoin('p.Milestone m')
	  ->leftJoin('m.Task t')
	  ->leftJoin('t.Resource r')
	  ->where('p.createdby = ?', sfContext::getInstance()->getUser()->getResourceId())
	  ->groupBy('m.projectid')
	  ->execute();

	return $resourceCostPerMilestone;
  }
  public function getResourceCountTaskCountMilestoneCount($projectId=null)
  {
    $resourceId = sfContext::getInstance()->getUser()->getGuardUser()->getResource()->getResourceid();

    $result = Doctrine_Query::create()->select('p.projectid, p.projectname, count(Distinct m.milestoneid) AS milestone_count, count(Distinct t.taskid) AS task_count, count(Distinct t.resourceid) AS resource_count')
            ->from('Project p')
			->leftJoin('p.Milestone m')
			->leftJoin('m.Task t')            
            ->where('p.createdby = ?', $resourceId)
            ->groupBy('p.projectname, p.projectid')
            ->execute();
	$projectsWithResourceCountTaskCountMilestoneCount = array();

	foreach ($result as $item)
	{	  
	  $projectsWithResourceCountTaskCountMilestoneCount[] = array('project_id' => $item->getProjectid(), 'project_name' => $item->getProjectname(), 'project_name_slug' => SmartManager::slugify($item->getProjectname()), 'milestone_count' => $item->getMilestoneCount(), 'task_count' => $item->getTaskCount(), 'resource_count' => $item->getResourceCount());
	}
	return $projectsWithResourceCountTaskCountMilestoneCount;	
  }
  public function getProjectsOfLoggedInUser()
  {
    return sfContext::getInstance()->getUser()->getGuardUser()->getResource()->getProjectsOfLoggedInUser();
  }
}